package com.h3bpm.web.reback.controller;

import OThinker.Common.DotNetToJavaStringHelper;
import OThinker.Common.Organization.Models.User;
import OThinker.H3.Controller.ControllerBase;
import OThinker.H3.Entity.DataModel.BizObject;
import OThinker.H3.Entity.Instance.Data.InstanceData;
import OThinker.H3.service.util.AssertUtil;
import com.alibaba.fastjson.JSONObject;
import com.h3bpm.base.util.AppUtility;
import com.h3bpm.web.common.utils.SqlUtil;
import com.h3bpm.web.reback.service.RebackService;
import com.h3bpm.web.util.DateUtil;
import data.DataRow;
import data.DataTable;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 数据回写经营系统
 *
 * @author ：cjj
 * @date ：Created in 2020/8/27 18:06
 */
@RestController
@RequestMapping("/Portal/WritebackController")
public class WritebackController extends ControllerBase{

    private final static Logger logger = LoggerFactory.getLogger(WritebackController.class);

    private static String dateFormatStr = "yyyy-MM-dd";

    private static String dataSources = "EC";

    @Autowired
    private RebackService rebackService;

    @Override
    public String getFunctionCode() {
        return null;
    }


    /**
     * 流程数据回写
     * @param instanceId 流程实例ID
     * @return
     */
    @RequestMapping("/goBack")
    @ResponseBody
    public JSONObject goback(String instanceId,String workflowCode){
        logger.info("回写OA数据库intanceId:"+instanceId+"-流程编码："+workflowCode);
        JSONObject result = new JSONObject();
        boolean flag = false;
        AssertUtil.assertNotNull(instanceId);
        if(StringUtils.isBlank(instanceId)||StringUtils.isBlank(workflowCode)){
            result.put("code","500");
            result.put("data", flag);
            result.put("msg","数据为空！");
            return result;
        }
        //咨询报告号新增申请
        if("consultingReportNumber".equals(workflowCode)){
            flag = rebackService.consultingReportNumber(instanceId, workflowCode, null);
        }
        //招标代理号新增申请
        else if("applicationAgencyNo".equals(workflowCode)){
            flag = rebackService.applicationAgencyNo(instanceId, workflowCode, null);
        }
        //造价报告号新增
        else if("costReportNo".equals(workflowCode)){
            flag = rebackService.costReportNo(instanceId, workflowCode, null);
        }
        //项目咨询报告盖章申请
        else if("ConsultingOutcomeDocument".equals(workflowCode)){
            flag = consultingOutcomeDocument(instanceId, workflowCode);
        }
        //招标文件盖章申请
        else if("biddingDocument".equals(workflowCode)){
            flag = biddingDocument(instanceId, workflowCode);
        }
        //造价咨询报告盖章申请
        else if("costConsultingReportSeal".equals(workflowCode)){
            flag = costConsultingReportSeal(instanceId, workflowCode);
        }
        //监理规划报审
        else if("supervisionPlanningReport".equals(workflowCode)){
            flag = supervisionPlanningReport(instanceId, workflowCode);
        }
        //项目管理成果文件报审
        else if("projectManageResultsReview".equals(workflowCode)){
            flag = projectManageResultsReview(instanceId, workflowCode);
        }
        //环境咨询成果文件盖章申请
        else if("environmentalResultReview".equals(workflowCode)){
            flag = environmentalResultReview(instanceId, workflowCode);
        }
        //项目资料盖章申请
        else if("ProjectInformationSeal".equals(workflowCode)){
            flag = projectInformationSeal(instanceId, workflowCode);
        }
        //招标代理过程文件盖章申请
        else if("tenderAgentFileSeal".equals(workflowCode)){
            flag = tenderAgentFileSeal(instanceId, workflowCode);
        }
        //项目资料电子归档流程
        else if("projectElectronicArchive".equals(workflowCode)){
            flag = projectElectronicArchive(instanceId, workflowCode);
        }
        //项目书面档案归档
        else if("projectWrittenFiling".equals(workflowCode)){
            flag = projectWrittenFiling(instanceId, workflowCode);
        }
        //招标信息发布申请
        else if("biddingInfoApplication".equals(workflowCode)){
            flag = biddingInfoApplication(instanceId, workflowCode);
        }
        if(flag){
            result.put("code","200");
            result.put("msg","success");
            result.put("data", flag);

        }else{
            try {
                //线程休眠30秒，bpm流程业务服务接口异常
                Thread.sleep(30000);
            } catch (InterruptedException e) {
                logger.error("线程运行异常，详细信息："+e);
                e.printStackTrace();
            }
            result.put("code","500");
            result.put("msg","fail");
            result.put("data", flag);
        }
        return result;
    }

    /**
     * 招标信息发布申请回写
     *
     * @param instanceId
     * @param workflowCode
     * @return
     */
    public boolean biddingInfoApplication(String instanceId, String workflowCode) {
        /*String insetSql = "insert into OA_yw_实施流程_招标代理项目招标信息发布_TJEC (ID_3192, 项目代码, 招标代理号, " +
                "发布内容, 公告媒体 , 其它媒体说明, 申请备注, 填表人, 填表日期, " +
                "中采网发布日期, 沪建筑业网发布日期, 沪建筑业网发布日期, 外网发布日期, 其它发布日期, 中采网发布操作人, " +
                "沪建筑业网发布操作人, 沪采网发布操作人, 外网发布操作人, 其它发布操作人, 办理备注, 中国政府采购网发布日期, " +
                "中国政府采购网发布操作人, 中国金融集采网发布日期, 中国金融集采网发布操作人, 填表方法) " +
                "values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'," +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s' , '%s')";*/
        String insetSql = "insert into OA_yw_实施流程_招标代理项目招标信息发布_TJEC (ID_3192, 项目代码, 招标代理号, " +
                "发布内容, 公告媒体 , 其它媒体说明, 发布日期, 申请备注, 填表人, 填表日期, 办理备注, 数据来源, 项目名称, 项目负责人, 项目所在公司, 项目执行部门) " +
                "values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
       /* String insetSql = "insert into OA_yw_实施台账_招标代理管理 (ID_3177, 项目代码, 项目全局ID, " +
                "业务类别全局ID, 标段名称, 业务类别, 业务ID, 招标代理号, 报建编号或招标编号, 标段编号或招标编码, 公告发布日期," +
                "公告媒体, 中标登记资料归档, 其他媒体, 发布办理备注, 数据来源) values('%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";*/
        String updateSql = "update OA_yw_实施台账_招标代理管理 set 公告发布日期 ='%s', 公告媒体 ='%s', 中标登记资料归档 ='%s', 其他媒体 ='%s',"+
                "发布办理备注 ='%s', 是否发布媒体 ='%s' where 招标代理号 ='%s'";
        String selectSql = "select 招标代理号 from OA_yw_实施台账_招标代理管理 where 招标代理号='%s'";
        boolean flag= false;
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //业务类别ID
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //标段名称
            String sectionName = (String)bizObject.GetValue("sectionName");
            //业务类别
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //招标代理号
            String tenderAgentNo = (String)bizObject.GetValue("tenderAgentNo");
            //沪报建编号（或招标编号）
            String constructionNoSH = (String)bizObject.GetValue("constructionNoSH");
            //沪标段号（或其他编号）
            String sectionNoSH = (String)bizObject.GetValue("sectionNoSH");
            //公告发布日期
            String publishDate2 = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("publishDate2"), dateFormatStr));
            //公告媒体
            String publishChannel = (String)bizObject.GetValue("publishChannel");
            publishChannel = publishChannel==null ? "": publishChannel.replace(";",",");
            //中标登记资料归档
            String biddingRegisterCheck = (String)bizObject.GetValue("biddingRegisterCheck");
            biddingRegisterCheck = biddingRegisterCheck==null ? "": biddingRegisterCheck.replace(";",",");
            //其他媒体
            String otherMedia = (String)bizObject.GetValue("otherMedia");
            //发布办理备注
            String publishRemark = (String)bizObject.GetValue("publishRemark");
            //发布内容
            String publishContent = (String)bizObject.GetValue("publishContent");
            //申请备注
            String applicationNote = (String)bizObject.GetValue("applicationNote");
            //填表人
            String fillName = (String)bizObject.GetValue("fillName");
            //填表日期
            String fillDate = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("fillDate"), dateFormatStr));
            //填表人
            String handleRemarks = (String)bizObject.GetValue("handleRemarks");
            //项目名称
            String projectName = (String)bizObject.GetValue("projectName");
            //项目负责人
            String projectLeader = (String)bizObject.GetValue("projectLeader");
            //项目所在公司
            String projectCompany = (String)bizObject.GetValue("projectCompany");
            //项目执行部门
            String deptName = (String)bizObject.GetValue("deptName");
            //是否发布媒体
            String publicFlag = (String)bizObject.GetValue("publicFlag");
            int maxId =  rebackService.getMaxId("[ezoffice].[whir$t3192]", "whir$t3192_id");
            String sql = String.format(insetSql, maxId+1, projectCode, tenderAgentNo, publishContent, publishChannel, otherMedia,
                    publishDate2, applicationNote, fillName, fillDate, handleRemarks, dataSources, projectName, projectLeader, projectCompany, deptName);
            logger.debug("招标信息发布申请回写视图'OA_yw_实施流程_招标代理项目招标信息发布_TJEC'SQL:"+sql);
            flag = rebackService.performSql(sql);
            if(!flag){
                return flag;
            }
            //查询造价报告号是否存在表中
            List<Map<String, Object>> list = rebackService.queryForList(String.format(selectSql, tenderAgentNo));
            if(CollectionUtils.isEmpty(list)){
                logger.error("招标代理号:"+tenderAgentNo+"在表‘OA_yw_实施台账_招标代理管理’中不存在");
                flag = false;
                rebackService.saveWriteBackLog(workflowCode, "招标代理号:"+tenderAgentNo+"在表‘OA_yw_实施台账_招标代理管理’中不存在", instanceId, flag?"success":"fail");
                return flag;
            }else{
                String updateSqls = String.format(updateSql, publishDate2, publishChannel, biddingRegisterCheck, otherMedia, publishRemark, publicFlag, tenderAgentNo);
                logger.debug("招标信息发布申请回写视图‘OA_yw_实施台账_招标代理管理’SQL:"+updateSqls);
                flag = rebackService.performSql(updateSqls);
            }
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 招标文件盖章申请回写
     *
     * @param instanceId
     * @return
     */
    @Transactional
    public boolean biddingDocument(String instanceId, String workflowCode){
        boolean flag = false;
       /* String insertSql = "insert into OA_yw_实施台账_招标代理过程文件 (ID_3256, 项目全局ID, 项目代码, 招标代理号全局ID, " +
                "招标代理号, 文件名称, 备注, 盖章日期) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s') ";*/
        String insertSql = "insert into OA_yw_实施台账_招标代理过程文件 (ID_3256, 项目全局ID, 项目代码, " +
                "招标代理号, 文件名称, 备注, 盖章日期, 数据来源, 业务ID) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s') ";
        String updateTenderAgentManageSql = "update OA_yw_实施台账_招标代理管理 set 标段名称 ='%s', 招标师姓名 ='%s' where 招标代理号 ='%s'";
        String selectTenderAgentManageSql = "select 招标代理号 from OA_yw_实施台账_招标代理管理 where 招标代理号 ='%s'";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //招标代理号全局ID
//            String agencyId = (String)bizObject.GetValue("agencyId");
            //招标代理号
            String biddingAgencyNo = (String)bizObject.GetValue("biddingAgencyNo");
            //盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //获取盖章明细
            List<BizObject> sealedDetails = new ArrayList<>();
            if(!Objects.isNull(bizObject.GetValue("sealedDetails"))) {
                sealedDetails = Arrays.asList((BizObject[]) bizObject.GetValue("sealedDetails"));
            }
            for (int i = 0; i < sealedDetails.size(); i++) {
                BizObject bizObjectRow = sealedDetails.get(i);
                //文件名称
                String fileName = (String)bizObjectRow.GetValue("fileName");
                //备注
                String sealRemark = (String)bizObjectRow.GetValue("sealRemark");
//                int maxId = rebackService.getMaxId("OA_yw_实施台账_招标代理过程文件", "ID_3256");
                int maxId = rebackService.getMaxId("[ezoffice].[whir$t3256]", "whir$t3256_id");
//                String sql = String.format(insertSql, maxId+1, projectGlobalId, projectCode, agencyId, biddingAgencyNo, fileName, sealRemark, sealTime);
                String sql = String.format(insertSql, maxId+1, projectGlobalId, projectCode, biddingAgencyNo, fileName, sealRemark, sealTime, dataSources, businessId);
                logger.debug("招标文件盖章申请回写添加‘OA_yw_实施台账_招标代理过程文件’表SQL:"+sql);
                flag = rebackService.performSql(sql);
                if(!flag) {
                    break;
                }
            }

            if(!flag){
                return flag;
            }
            //标段名称
            String noName = (String)bizObject.GetValue("noName");
            //招标师姓名
            String tendererName = (String)bizObject.GetValue("tendererName");
            //查询招标代理号是否存在表中
            List<Map<String, Object>> list = rebackService.queryForList(String.format(selectTenderAgentManageSql, biddingAgencyNo));
            if(CollectionUtils.isEmpty(list)){
                logger.error("招标代理号："+biddingAgencyNo+"在表‘OA_yw_实施台账_招标代理管理’中不存在");
                flag = false;
                rebackService.saveWriteBackLog(workflowCode, "招标代理号："+biddingAgencyNo+"在表‘OA_yw_实施台账_招标代理管理’中不存在", instanceId, flag?"success":"fail");
                return flag;
            }else{
                String sql = String.format(updateTenderAgentManageSql, noName, tendererName, biddingAgencyNo);
                logger.debug("招标文件盖章申请回写修改表‘OA_yw_实施台账_招标代理管理’SQL:"+sql);
                flag = rebackService.performSql(sql);
            }
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 项目咨询报告盖章申请回写
     *
     * @param instanceId
     * @return
     */
    @Transactional
    public boolean consultingOutcomeDocument(String instanceId, String workflowCode){
        boolean flag = false;
        String updateSql = "update OA_yw_实施台帐_咨询报告管理 set 项目全局ID = '%s', 项目代码 = '%s', 业务类别全局ID = '%s', " +
                "业务类别 = '%s', 咨询报告号 = '%s', 报告名称 = '%s', 报告提交部门 = '%s', 报告用途 = '%s', 报告编制人 = '%s', 报告编制日期 = '%s', 报告使用印章 = '%s', " +
                "报告使用其它电子印章 = '%s', 报告盖章份数 = '%s', 报告取件人 = '%s', 报告取件人联系电话 = '%s', 报告申请类别 = '%s', 报告申请备注 = '%s'," +
                "报告盖章人 = '%s', 报告盖章日期 = '%s', 报告盖章备注 = '%s', 服务范围 = '%s', 服务范围小类 = '%s', 项目性质 = '%s', 合同号或批复号 = '%s', " +
                "项目专业 = '%s', 项目专业小类 = '%s', 地区 = '%s', 境内或境外 = '%s', 投资方 = '%s', 投资额_万元 = '%s', 工程咨询成果完成日期 = '%s', " +
                "拟开工时间 = '%s', 项目境外投资额 = '%s', 核增减投资额 = '%s', 资金来源 = '%s', 项目核准审核机关 = '%s', 是否否决 = '%s', 完成方式 = '%s', " +
                "申请状态 = '%s', 建设规模_产能 = '%s', 备注 = '%s', 业务名称 = '%s', 业务ID = '%s' where 咨询报告号 = '%s'";
        //签章不包含注册咨询师电子章
        String updateSql2 = "update OA_yw_实施台帐_咨询报告管理 set 项目全局ID = '%s', 项目代码 = '%s', 业务类别全局ID = '%s', " +
                " 业务类别 = '%s', 咨询报告号 = '%s', 报告名称 = '%s', 报告提交部门 = '%s', 报告用途 = '%s', 报告编制人 = '%s', 报告编制日期 = '%s', 报告使用印章 = '%s'," +
                " 报告使用其它电子印章 = '%s', 报告盖章份数 = '%s', 报告取件人 = '%s', 报告取件人联系电话 = '%s', 报告申请类别 = '%s', 报告申请备注 = '%s'," +
                " 报告盖章人 = '%s', 报告盖章日期 = '%s', 报告盖章备注 = '%s',  业务名称 = '%s', 业务ID = '%s' where 咨询报告号 = '%s'";
        String childInsertSql = "insert into OA_yw_实施台账_注册咨询师电子章用章记录 (ID_3426, 咨询报告号, 姓名, 证件号, 主专业, 辅助专业, 职责, 数据来源, 业务ID) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        String selectSql = "select 咨询报告号 from OA_yw_实施台帐_咨询报告管理 where 咨询报告号 = '%s'";
        String childDelSql = "delete from OA_yw_实施台账_注册咨询师电子章用章记录 where 咨询报告号 = '%s'";
        String insetSql = "insert into OA_yw_实施流程_项目咨询报告盖章申请 (ID_3219, 项目代码, 成果文件名称, 报告提交部门, 报告用途," +
                "报告编制人, 报告编制日期, 报告电子文件上传, 报告使用印章, 用印文件类别, 报告盖章份数, 报告取件人, 报告取件人联系电话," +
                "报告申请类别, 报告申请备注, 报告盖章人, 报告盖章日期, 报告盖章备注, 数据来源, 咨询报告号, 业务ID, 项目名称, 项目负责人, " +
                "项目所在公司, 项目执行部门) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //业务类别全局ID
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //业务类别
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //咨询报告号
            String reportNo = (String)bizObject.GetValue("reportNo");
            //报告名称
            String resultDocumentName = (String)bizObject.GetValue("resultDocumentName");
            //报告提交部门
            String submissionDepartment = (String)bizObject.GetValue("submissionDepartment");
            //报告用途
            String reportingPurposes = (String)bizObject.GetValue("reportingPurposes");
            //报告编制人
            String preparePeoples = (String)bizObject.GetValue("preparePeoples");
            //报告编制日期
            String prepareTime = clearDefaultDate(DateUtil.format((Date) bizObject.GetValue("prepareTime"), dateFormatStr));
            //报告使用印章
            String useSeal = (String)bizObject.GetValue("useSeal");
            useSeal = useSeal==null ? "": useSeal.replace(";",",");
            //报告使用其它电子印章
            String otherSeal = (String)bizObject.GetValue("otherSeal");
            //报告盖章份数
            String sealNum = String.valueOf(bizObject.GetValue("sealNum"));
            //报告取件人
            String takePersons = (String)bizObject.GetValue("takePersons");
            //报告取件人联系电话
            String phone = (String)bizObject.GetValue("phone");
            //报告申请类别
            String applicationCategory = (String)bizObject.GetValue("applicationCategory");
            //报告申请备注
            String applicationNote = (String)bizObject.GetValue("applicationNote");
            //报告盖章人
            String sealers = (String)bizObject.GetValue("sealers");
            //报告盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //报告盖章备注
            String handleRemark = (String)bizObject.GetValue("handleRemark");
            //服务范围
            String serviceScope = (String)bizObject.GetValue("serviceScope");
            //服务范围小类
            String serviceScopelit = (String)bizObject.GetValue("serviceScopelit");
            //项目性质
            String projectNature = (String)bizObject.GetValue("projectNature");
            //合同号或批复号
            String contractNumber = (String)bizObject.GetValue("contractNumber");
            //项目专业
            String projectProfessional = (String)bizObject.GetValue("projectProfessional");
            //项目专业小类
            String projectProfessionalLit = (String)bizObject.GetValue("projectProfessionalLit");
            //地区（项目所在地区）
            String region = (String)bizObject.GetValue("region");
            //境内或境外
            String Inside = (String)bizObject.GetValue("Inside");
            //投资方
            String investor = (String)bizObject.GetValue("investor");
            //投资额（万元）
            String investment = (String)bizObject.GetValue("investment");
            //工程咨询成果完成日期
            String endTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("endTime"), dateFormatStr));
            //拟开工时间
            String proposedStartTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("proposedStartTime"), dateFormatStr));
            //项目境外投资额
            String OverseasInvestment = (String)bizObject.GetValue("OverseasInvestment");
            //核增减投资额
            String NuclearIncrease = (String)bizObject.GetValue("NuclearIncrease");
            //资金来源
            String SourcesFunding = (String)bizObject.GetValue("SourcesFunding");
            //项目核准审核机关
            String examinationAuthority = (String)bizObject.GetValue("examinationAuthority");
            //是否否决
            String WhetherVeto = (String)bizObject.GetValue("WhetherVeto");
            //完成方式
            String CompleteWay = (String)bizObject.GetValue("CompleteWay");
            //申请状态
            String ApplicationStatus = (String)bizObject.GetValue("ApplicationStatus");
            //建设规模（产能）
            String capacity = (String)bizObject.GetValue("capacity");
            //备注
            String remake = (String)bizObject.GetValue("remake");
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //项目名称
            String projectName = (String)bizObject.GetValue("projectName");
            //项目负责人
            String projectLeader = (String)bizObject.GetValue("projectLeader");
            //项目所在公司
            String projectCompany = (String)bizObject.GetValue("projectCompany");
            //项目执行部门
            String deptName = (String)bizObject.GetValue("deptName");
            //咨询工程师
            List<BizObject> consultingEngineers = new ArrayList<>();
            if(!Objects.isNull(bizObject.GetValue("ConsultingEngineer"))){
                consultingEngineers = Arrays.asList((BizObject[])bizObject.GetValue("ConsultingEngineer"));
            }
            logger.info("项目咨询报告盖章为:"+useSeal);
            //查询咨询报告号是否存在表中
            List<Map<String, Object>> list = rebackService.queryForList(String.format(selectSql, reportNo));
            if(CollectionUtils.isEmpty(list)){
                logger.error("咨询报告号："+reportNo+"在表‘OA_yw_实施台帐_咨询报告管理’中不存在");
                flag = false;
                rebackService.saveWriteBackLog(workflowCode, "咨询报告号："+reportNo+"在表‘OA_yw_实施台帐_咨询报告管理’中不存在", instanceId, flag?"success":"fail");
            }else{
                if(useSeal.indexOf("注册咨询师电子章")>-1){
                    logger.info("包含注册咨询师电子章");
                    int maxId = (rebackService.getMaxId("[ezoffice].[whir$t3426]", "whir$t3426_id"))+1;
                    for (int i = 0; i < consultingEngineers.size(); i++) {
                        BizObject bizObjectRow = consultingEngineers.get(i);
                        //姓名
                        String engineerName = (String)bizObjectRow.GetValue("engineerName");
                        //证件号
                        String IdNo = (String)bizObjectRow.GetValue("IdNo");
                        //主专业
                        String professional = (String)bizObjectRow.GetValue("professional");
                        //辅助专业
                        String SecondaryProfessional = (String)bizObjectRow.GetValue("SecondaryProfessional");
                        //职责
                        String responsibilities = (String)bizObjectRow.GetValue("responsibilities");
                        if(StringUtils.isNotBlank(reportNo)){
                            if(i==0){
                                rebackService.performSql(String.format(childDelSql, reportNo));
                            }
//                        int maxId = rebackService.getMaxId("OA_yw_实施台账_注册咨询师电子章用章记录", "ID_3426");
                             logger.debug(i+"maxId ："+maxId);
                            String childInsertSqls = String.format(childInsertSql , maxId+i, reportNo, engineerName, IdNo, professional, SecondaryProfessional, responsibilities, dataSources, businessId);
                            logger.debug("项目咨询报告盖章申请回写新增表‘OA_yw_实施台账_注册咨询师电子章用章记录’SQL:"+childInsertSqls);
                            logger.debug("childInsertSqls："+childInsertSqls);
                            rebackService.performSql(childInsertSqls);
                        }
                    }
                    String sql = String.format(updateSql, projectGlobalId, projectCode, businessCategoryId, businessCategory, reportNo,
                            resultDocumentName, submissionDepartment, reportingPurposes, preparePeoples, prepareTime, useSeal, otherSeal, sealNum,
                            takePersons, phone, applicationCategory, applicationNote, sealers, sealTime, handleRemark, serviceScope, serviceScopelit,
                            projectNature, contractNumber, projectProfessional, projectProfessionalLit, region, Inside, investor, investment, endTime, proposedStartTime,
                            OverseasInvestment, NuclearIncrease, SourcesFunding, examinationAuthority, WhetherVeto, CompleteWay, ApplicationStatus,
                            capacity, remake, resultDocumentName, businessId, reportNo);
                    logger.debug("项目咨询报告盖章申请回写SQL:"+sql);
                    logger.info("项目咨询报告盖章申请回写SQL:"+sql);
                    flag = rebackService.performSql(sql);
                }else{
                    logger.info("不包含注册咨询师电子章");
                    String sql = String.format(updateSql2, projectGlobalId, projectCode, businessCategoryId, businessCategory, reportNo,
                            resultDocumentName, submissionDepartment, reportingPurposes, preparePeoples, prepareTime, useSeal, otherSeal, sealNum,
                            takePersons, phone, applicationCategory, applicationNote, sealers, sealTime, handleRemark,  resultDocumentName, businessId, reportNo);
                    logger.debug("项目咨询报告盖章申请回写SQL:"+sql);
                    flag = rebackService.performSql(sql);
                }
            }
            //回写项目OA_yw_实施流程_项目咨询报告盖章申请表
            //附件
            String att = getAttachmentByObjectId(bizObject.getObjectID());
            //报告文件类别
            String reportCategory = (String)bizObject.GetValue("reportCategory");
            int insertMaxId = rebackService.getMaxId("[ezoffice].[whir$t3219]", "whir$t3219_id");
            String insetSqls = String.format(insetSql , insertMaxId+1, projectCode, resultDocumentName, submissionDepartment, reportingPurposes, preparePeoples, prepareTime,
                    att, useSeal, reportCategory, sealNum, takePersons, phone, applicationCategory, applicationNote, sealers, sealTime, handleRemark, dataSources, reportNo,
                    businessId, projectName, projectLeader, projectCompany, deptName);
            logger.debug("项目咨询报告盖章申请回写新增表‘OA_yw_实施流程_项目咨询报告盖章申请’SQL:"+insetSqls);
            rebackService.performSql(insetSqls);
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 造价咨询报告盖章申请回写
     *
     * @param instanceId
     * @return
     */
    @Transactional
    public boolean costConsultingReportSeal(String instanceId, String workflowCode){
        boolean flag = false;
        /*String updateSql = "update OA_yw_实施台账_造价报告管理 set 项目全局ID ='%s', 项目代码 ='%s', 造价报告号全局ID ='%s', 造价报告号 ='%s', " +
                "报告名称 ='%s', 审定日期 ='%s', 审核人 ='%s', 复核人 ='%s', 主要参与人 ='%s', 送审单位 ='%s', 送审价_元 =%s," +
                "审定价_元 =%s, 核增额_元 =%s, 核减额_元 =%s, 累计核增减额_元 =%s, 申请日期 ='%s', 盖章日期 ='%s' where 造价报告号 ='%s'";*/
        String updateSql = "update OA_yw_实施台账_造价报告管理 set 项目全局ID ='%s', 项目代码 ='%s', 造价报告号 ='%s', " +
                "报告名称 ='%s', 审定日期 ='%s', 审核人 ='%s', 复核人 ='%s', 主要参与人 ='%s', 送审单位 ='%s', 送审价_元 =%s," +
                "审定价_元 =%s, 核增额_元 =%s, 核减额_元 =%s, 累计核增减额_元 =%s, 盖章日期 ='%s', 报告总价_元 ='%s', 业务名称 ='%s', 业务ID ='%s' where 造价报告号 ='%s'";
        String selectSql = "select 造价报告号 from OA_yw_实施台账_造价报告管理 where 造价报告号 ='%s'";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //造价报告号全局ID
//            String costReportId = (String)bizObject.GetValue("costReportId");
            //造价报告号
            String costReportNo = (String)bizObject.GetValue("costReportNo");
            //报告名称
            String resultDocumentName = (String)bizObject.GetValue("resultDocumentName");
            //审定日期
            String approvalDate = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("approvalDate"), dateFormatStr));
            //审核人
            String reviewer = (String)bizObject.GetValue("reviewer");
            //复核人
            String reviewOne = (String)bizObject.GetValue("reviewOne");
            //主要参与人
            String principalParticipant = (String)bizObject.GetValue("principalParticipant");
            //送审单位
            String department = (String)bizObject.GetValue("department");
            //送审价_元
            String sendNumber = String.valueOf(bizObject.GetValue("sendNumber"));
            //审定价_元
            Double checkPriceNumber = (Double)bizObject.GetValue("checkPriceNumber");
            //核增额_元
            Double nuclearNumber = (Double)bizObject.GetValue("nuclearNumber");
            //核减额_元
            Double deductionNo = (Double)bizObject.GetValue("deductionNo");
            //累计核增减额_元
            Double increaseNos = (Double)bizObject.GetValue("increaseNos");
            //申请日期
            String dateFilling = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("dateFilling"), dateFormatStr));
            //盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //报告总价
            Double totalPrice = Double.parseDouble(DotNetToJavaStringHelper.isNullOrEmpty((String)bizObject.GetValue("totalPrice"))?"0":(String)bizObject.GetValue("totalPrice"));
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //查询造价报告号是否存在表中
            List<Map<String, Object>> list = rebackService.queryForList(String.format(selectSql, costReportNo));
            if(CollectionUtils.isEmpty(list)){
                logger.error("造价报告号:"+costReportNo+"在表‘OA_yw_实施台账_造价报告管理’中不存在");
                flag = false;
                rebackService.saveWriteBackLog(workflowCode, "造价报告号:"+costReportNo+"在表‘OA_yw_实施台账_造价报告管理’中不存在", instanceId, flag?"success":"fail");
                return flag;
            }else{
                /*String sql = String.format(updateSql, projectGlobalId, projectCode, costReportId, costReportNo, resultDocumentName,
                        approvalDate, reviewer, reviewOne, principalParticipant, department, sendNumber, checkPriceNumber, nuclearNumber,
                        deductionNo, increaseNos, dateFilling, sealTime, costReportNo);*/
                String sql = String.format(updateSql, projectGlobalId, projectCode, costReportNo, resultDocumentName,
                        approvalDate, reviewer, reviewOne, principalParticipant, department, sendNumber, checkPriceNumber, nuclearNumber,
                        deductionNo, increaseNos, sealTime, totalPrice, resultDocumentName, businessId, costReportNo);
                logger.debug("造价咨询报告盖章申请回写SQL:"+sql);
                flag = rebackService.performSql(sql);
            }
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 监理规划报审回写
     *
     * @param instanceId
     * @return
     */
    public boolean supervisionPlanningReport(String instanceId, String workflowCode){
        boolean flag = false;
        /*String insetSql = "insert into OA_yw_实施流程_项目规划报审 (ID_3234, 项目代码, 项目全局ID, 任命手续, " +
                "业务类别全局ID, 服务类别, 报审文件, 报审类型, 附件, 使用印章, 盖章份数, 取件人, 申请备注, " +
                "填表人, 填表日期, 盖章人, 盖章日期, 盖章办理备注) values('%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";*/
        String insetSql = "insert into OA_yw_实施流程_项目规划报审 (ID_3234, 项目代码, 项目全局ID, 任命手续, " +
                "业务类别全局ID, 服务类别, 报审文件, 报审类型, 附件, 使用印章, 盖章份数, 取件人, 申请备注, " +
                "填表人, 填表日期, 盖章人, 盖章日期, 盖章办理备注, 数据来源, 业务ID, 项目名称) values('%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //任命手续
            String appointmentProcedures = (String)bizObject.GetValue("appointmentProcedures");
            //业务类别全局ID
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //服务类别（业务类别）
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //造价报告号全局ID
//            String costReportNoGlobalId = (String)bizObject.GetValue("costReportNoGlobalId");
            //报审文件（用印文件类别）
            String reviewFile = (String)bizObject.GetValue("reviewFile");
            //报审类型
            String reviewType = (String)bizObject.GetValue("reviewType");
            //附件
            String attachment = getAttachmentByObjectId(bizObject.getObjectID());
            //使用印章
            String useSeal = (String)bizObject.GetValue("useSeal");
            useSeal = useSeal==null ? "": useSeal.replace(";",",");
            //盖章份数
            String sealNums = String.valueOf(bizObject.GetValue("sealNums"));
            //取件人
            String takePersons = (String)bizObject.GetValue("takePersons");
            //申请备注
            String applicationNote = (String)bizObject.GetValue("applicationNote");
            //填表人
            String filler = (String)bizObject.GetValue("filler");
            //填表日期
            String fillDate = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("fillDate"), dateFormatStr));
            //盖章人
            String sealers = (String)bizObject.GetValue("sealers");
            //盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //办理备注
            String handleRemark = (String)bizObject.GetValue("handleRemark");
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //项目名称
            String projectName = (String)bizObject.GetValue("projectName");
//            int maxId = rebackService.getMaxId("OA_yw_实施流程_项目规划报审", "ID_3234");
            int maxId = rebackService.getMaxId("[ezoffice].[whir$t3234]", "whir$t3234_id");
            /*String sql = String.format(insetSql, maxId+1 , projectCode, projectGlobalId, appointmentProcedures, businessCategoryId,
                    businessCategory, reviewFile, reviewType, attachment, useSeal, sealNums, takePersons, applicationNote,
                    filler, fillDate, sealers, sealTime, handleRemark);*/
            String sql = String.format(insetSql, maxId+1 , projectCode, projectGlobalId, appointmentProcedures,
                    businessCategoryId, businessCategory, reviewFile, reviewType, attachment, useSeal, sealNums, takePersons, applicationNote,
                    filler, fillDate, sealers, sealTime, handleRemark, dataSources, businessId, projectName);
            logger.debug("监理规划报审回写SQL:"+sql);
            flag = rebackService.performSql(sql);
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 项目管理成果文件报审回写
     *
     * @param instanceId
     * @return
     */
    public boolean projectManageResultsReview(String instanceId, String workflowCode){
        boolean flag = false;
        /*String insetSql = "insert into OA_yw_实施流程_项目规划报审 (ID_3234, 项目代码, 项目全局ID, 任命手续, " +
                "业务类别全局ID, 服务类别, 报审文件, 报审类型, 附件, 使用印章, 盖章份数, 取件人, 申请备注, " +
                "填表人, 填表日期, 盖章人, 盖章日期, 盖章办理备注) values('%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";*/
        String insetSql = "insert into OA_yw_实施流程_项目规划报审 (ID_3234, 项目代码, 项目全局ID, 任命手续, " +
                "业务类别全局ID, 服务类别, 报审文件, 报审类型, 附件, 使用印章, 盖章份数, 取件人, 申请备注, " +
                "填表人, 填表日期, 盖章人, 盖章日期, 盖章办理备注, 数据来源, 业务ID, 项目名称) values('%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //任命手续
            String appointmentProcedures = (String)bizObject.GetValue("appointmentProcedures");
            //业务类别全局ID
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //服务类别（业务类别）
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //报审文件（用印文件类别）
            String reviewFile = (String)bizObject.GetValue("reviewFile");
            //报审类型
            String reviewType = (String)bizObject.GetValue("reviewType");
            //附件
            String attachment = getAttachmentByObjectId(bizObject.getObjectID());
            //使用印章
            String useSeal = (String)bizObject.GetValue("useSeal");
            useSeal = useSeal==null ? "": useSeal.replace(";",",");
            //盖章份数
            String sealNums = String.valueOf(bizObject.GetValue("sealNums"));
            //取件人
            String takePersons = (String)bizObject.GetValue("takePersons");
            //申请备注
            String applicationNote = (String)bizObject.GetValue("applicationNote");
            //填表人
            String filler = (String)bizObject.GetValue("filler");
            //填表日期
            String fillDate = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("fillDate"), dateFormatStr));
            //盖章人
            String sealers = (String)bizObject.GetValue("sealers");
            //盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //办理备注
            String handleRemark = (String)bizObject.GetValue("handleRemark");
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //项目名称
            String projectName = (String)bizObject.GetValue("projectName");
//            int maxId = rebackService.getMaxId("OA_yw_实施流程_项目规划报审", "ID_3234");
            int maxId = rebackService.getMaxId("[ezoffice].[whir$t3234]", "whir$t3234_id");
            /*String sql = String.format(insetSql, maxId+1 , projectCode, projectGlobalId, appointmentProcedures, businessCategoryId,
                    businessCategory, reviewFile, reviewType, attachment, useSeal, sealNums, takePersons, applicationNote,
                    filler, fillDate, sealers, sealTime, handleRemark);*/
            String sql = String.format(insetSql, maxId+1 , projectCode, projectGlobalId, appointmentProcedures,
                    businessCategoryId, businessCategory, reviewFile, reviewType, attachment, useSeal, sealNums, takePersons, applicationNote,
                    filler, fillDate, sealers, sealTime, handleRemark, dataSources, businessId, projectName);
            logger.debug("项目管理成果文件报审回写SQL:"+sql);
            flag = rebackService.performSql(sql);
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 环境咨询成果文件盖章申请回写
     *
     * @param instanceId
     * @return
     */
    public boolean environmentalResultReview(String instanceId, String workflowCode){
        boolean flag = false;
        /*String insetSql = "insert into OA_yw_实施流程_项目资料盖章申请 (ID_3182, 项目全局ID, 项目代码, 业务类别全局ID, " +
                "业务类别, 用印文件类别, 用印文件内容, 附件, 使用印章, 使用其它电子印章, 盖章份数, 取件人, " +
                "联系电话, 申请备注, 盖章人, 盖章日期, 办理备注) values('%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";*/
        String insetSql = "insert into OA_yw_实施流程_项目资料盖章申请 (ID_3182, 项目全局ID, 项目代码, 业务类别全局ID, " +
                "业务类别, 用印文件类别, 用印文件内容, 附件, 使用印章, 使用其它电子印章, 盖章份数, 取件人, " +
                "联系电话, 申请备注, 盖章人, 盖章日期, 办理备注, 数据来源) values('%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //业务类别全局ID
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //业务类别
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //用印文件类别
            String reportType = (String)bizObject.GetValue("reportType");
            //用印文件内容（报告名称）
            String reportName = (String)bizObject.GetValue("reportName");
            //附件
            String attachment = getAttachmentByObjectId(bizObject.getObjectID());
            //使用印章
            String useSeal = (String)bizObject.GetValue("useSeal");
            useSeal = useSeal==null ? "": useSeal.replace(";",",");
            //使用其它电子印章
            String otherElectronicSeals = (String)bizObject.GetValue("otherElectronicSeals");
            //盖章份数
            String sealNums = String.valueOf(bizObject.GetValue("sealNums"));
            //取件人
            String takePersons = (String)bizObject.GetValue("takePersons");
            //联系电话
            String phone = (String)bizObject.GetValue("phone");
            //申请备注
            String applicationNote = (String)bizObject.GetValue("applicationNote");
            //盖章人
            String sealers = (String)bizObject.GetValue("sealers");
            //盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //办理备注
            String handleRemark = (String)bizObject.GetValue("handleRemark");
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
//            int maxId = rebackService.getMaxId("OA_yw_实施流程_项目资料盖章申请", "ID_3182");
            int maxId = rebackService.getMaxId("[ezoffice].[whir$t3182]", "whir$t3182_id");
            /*String sql = String.format(insetSql, maxId+1 , projectGlobalId, projectCode, businessCategoryId, businessCategory,
                    reportType, reportName, attachment, useSeal, otherElectronicSeals, sealNums, takePersons,
                    phone, applicationNote, sealers, sealTime, handleRemark);*/
            String sql = String.format(insetSql, maxId+1, projectGlobalId, projectCode, businessCategoryId, businessCategory,
                    reportType, reportName, attachment, useSeal, otherElectronicSeals, sealNums, takePersons,
                    phone, applicationNote, sealers, sealTime, handleRemark, dataSources);
            logger.debug("环境咨询成果文件盖章申请回写SQL:"+sql);
            flag = rebackService.performSql(sql);
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId,"fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 项目资料盖章申请回写
     *
     * @param instanceId
     * @return
     */
    public boolean projectInformationSeal(String instanceId, String workflowCode){
        boolean flag = false;
        /*String insetSql = "insert into OA_yw_实施流程_项目资料盖章申请 (ID_3182, 项目全局ID, 项目代码, 业务类别全局ID, " +
                "业务类别, 用印文件类别, 用印文件内容, 附件, 使用印章, 使用其它电子印章, 盖章份数, 取件人, " +
                "联系电话, 申请备注, 盖章人, 盖章日期, 办理备注) values('%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";*/
        String insetSql = "insert into OA_yw_实施流程_项目资料盖章申请 (ID_3182, 项目全局ID, 项目代码, 业务类别全局ID, " +
                "业务类别, 用印文件类别, 用印文件内容, 附件, 使用印章, 使用其它电子印章, 盖章份数, 取件人, " +
                "联系电话, 申请备注, 盖章人, 盖章日期, 办理备注, 数据来源, 业务ID, 项目名称, 项目负责人, 项目所在公司, 项目执行部门) " +
                "values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //业务类别全局ID
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //业务类别
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //用印文件类别
            String reportType = (String)bizObject.GetValue("sealFileCategory");
            //用印文件内容（报告名称）
            String reportName = (String)bizObject.GetValue("sealFileContent");
            //附件
            String attachment = getAttachmentByObjectId(bizObject.getObjectID());
            //使用印章
            String useSeal = (String)bizObject.GetValue("useSeal");
            useSeal = useSeal==null ? "": useSeal.replace(";",",");
            //使用其它电子印章
            String otherElectronicSeals = (String)bizObject.GetValue("otherElectronicSeals");
            //盖章份数
            String sealNums = String.valueOf(bizObject.GetValue("sealNums"));
            //取件人
            String takePersons = (String)bizObject.GetValue("takePersons");
            //联系电话
            String phone = (String)bizObject.GetValue("phone");
            //申请备注
            String applicationNote = (String)bizObject.GetValue("applicationNote");
            //盖章人
            String sealers = (String)bizObject.GetValue("sealers");
            //盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //办理备注
            String handleRemark = (String)bizObject.GetValue("handleRemark");
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //项目名称
            String projectName = (String)bizObject.GetValue("projectName");
            //项目负责人
            String projectLeader = (String)bizObject.GetValue("projectLeader");
            //项目所在公司
            String projectCompany = (String)bizObject.GetValue("projectCompany");
            //项目执行部门
            String deptName = (String)bizObject.GetValue("deptName");
//            int maxId = rebackService.getMaxId("OA_yw_实施流程_项目资料盖章申请", "ID_3182");
            int maxId = rebackService.getMaxId("[ezoffice].[whir$t3182]", "whir$t3182_id");
           /* String sql = String.format(insetSql, maxId+1 , projectGlobalId, projectCode, businessCategoryId, businessCategory,
                    reportType, reportName, attachment, useSeal, otherElectronicSeals, sealNums, takePersons,
                    phone, applicationNote, sealers, sealTime, handleRemark);*/
            String sql = String.format(insetSql, maxId+1, projectGlobalId, projectCode, businessCategoryId, businessCategory,
                    reportType, reportName, attachment, useSeal, otherElectronicSeals, sealNums, takePersons,
                    phone, applicationNote, sealers, sealTime, handleRemark, dataSources, businessId, projectName,
                    projectLeader, projectCompany, deptName);
            logger.debug("项目资料盖章申请回写SQL:"+sql);
            flag = rebackService.performSql(sql);
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 招标代理过程文件盖章申请回写
     *
     * @param instanceId
     * @return
     */
    @Transactional
    public boolean tenderAgentFileSeal(String instanceId, String workflowCode){
        boolean flag = false;
    /*   String insertSql = "insert into OA_yw_实施台账_招标代理过程文件 (ID_3256, 项目全局ID, 项目代码, 招标代理号全局ID, " +
                "招标代理号, 文件名称, 备注, 盖章日期) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s') ";*/
        String insertSql = "insert into OA_yw_实施台账_招标代理过程文件 (ID_3256, 项目全局ID, 项目代码, " +
                "招标代理号, 文件名称, 备注, 盖章日期, 数据来源, 业务ID) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s') ";
        String updateTenderAgentManageSql = "update OA_yw_实施台账_招标代理管理 set 标段名称 ='%s', 招标师姓名 ='%s' where 招标代理号 ='%s'";
        String selectTenderAgentManageSql = "select 招标代理号 from OA_yw_实施台账_招标代理管理 where 招标代理号 ='%s'";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //项目全局ID
            String projectGlobalId = (String)bizObject.GetValue("projectGlobalId");
            //招标代理号全局ID
//            String agencyId = (String)bizObject.GetValue("agencyId");
            //招标代理号
            String biddingAgencyNo = (String)bizObject.GetValue("tenderAgentNo");
            //盖章日期
            String sealTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("sealTime"), dateFormatStr));
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
            //盖章文件明细
            List<BizObject> sealedDetails = new ArrayList<>();
            if(!Objects.isNull(bizObject.GetValue("sealFileDetails"))){
                sealedDetails = Arrays.asList((BizObject[])bizObject.GetValue("sealFileDetails"));
            }
            for (int i = 0; i < sealedDetails.size(); i++) {
                BizObject bizObjectRow = sealedDetails.get(i);
                //文件名称
                String fileName = (String)bizObjectRow.GetValue("fileName");
                //备注
                String sealRemark = (String)bizObjectRow.GetValue("remark");
//                int maxId = rebackService.getMaxId("OA_yw_实施台账_招标代理过程文件", "ID_3256");
                int maxId = rebackService.getMaxId("[ezoffice].[whir$t3256]", "whir$t3256_id");
//                String sql = String.format(insertSql, maxId+1, projectGlobalId, projectCode, agencyId, biddingAgencyNo, fileName, sealRemark, sealTime);
                String sql = String.format(insertSql, maxId+1, projectGlobalId, projectCode, biddingAgencyNo, fileName, sealRemark, sealTime, dataSources, businessId);
                logger.debug("招标代理过程文件盖章申请回写新增表‘OA_yw_实施台账_招标代理过程文件’SQL:"+sql);
                flag = rebackService.performSql(sql);
                if(!flag){
                    break;
                }
            }
            if(!flag){
                return flag;
            }
            //标段名称
            String noName = (String)bizObject.GetValue("sectionName");
            //招标师姓名
            String tendererName = (String)bizObject.GetValue("tendererName");
            //查询咨招标代理号是否存在表中
            List<Map<String, Object>> list = rebackService.queryForList(String.format(selectTenderAgentManageSql, biddingAgencyNo));
            if(CollectionUtils.isEmpty(list)){
                logger.error("招标代理号："+biddingAgencyNo+"在表‘OA_yw_实施台账_招标代理管理’中不存在");
                flag = false;
                rebackService.saveWriteBackLog(workflowCode, "招标代理号："+biddingAgencyNo+"在表‘OA_yw_实施台账_招标代理管理’中不存在", instanceId, flag?"success":"fail");
                return flag;
            }else{
                String sql = String.format(updateTenderAgentManageSql, noName, tendererName, biddingAgencyNo);
                logger.debug("招标代理过程文件盖章申请回写修改表‘OA_yw_实施台账_招标代理管理’SQL:"+sql);
                flag = rebackService.performSql(sql);
            }
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 项目资料电子归档申请表回写
     *
     * @param instanceId
     * @return
     */
    public boolean projectElectronicArchive(String instanceId, String workflowCode){
        boolean flag = false;
        /*String insetSql = "insert into OA_yw_实施流程_项目资料归档 (ID_3223, 项目代码, 归档服务类别, 招标代理号, " +
                "标段名称, 资料编制人, 资料编制日期, 资料名称, 装订方法, 归档数量, 归档形式, 归档审查公司, " +
                "送审日期, 审查人, 审查日期, 审查结论, 审查备注, 归档编号, 存放位置, 收件人, 收件日期, 入库备注)" +
                " values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";*/
        String insetSql = "insert into OA_yw_实施流程_项目资料归档 (ID_3223, 项目代码, 招标代理号, 业务类别, 业务类别全局ID," +
                "标段名称, 资料编制人, 资料编制日期, 资料名称, 装订方法, 归档数量, 归档形式, 归档审查公司, " +
                "送审日期, 审查人, 审查日期, 审查备注, 归档编号, 存放位置, 收件人, 收件日期, 入库备注, 数据来源, 业务ID)" +
                " values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //招标代理号
            String biddingAgencyNo = (String)bizObject.GetValue("biddingAgencyNo");
            //业务类别编码
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //业务类别
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //标段名称
            String noName = (String)bizObject.GetValue("NoName");
            //资料编制人
            String preparePeople = (String)bizObject.GetValue("preparePeople");
            //资料编制日期
            String prepareDate = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("prepareDate"), dateFormatStr));
            //资料名称
            String profileName = (String)bizObject.GetValue("profileName");
            //装订方法
            String bindingMethod = (String)bizObject.GetValue("bindingMethod");
            //归档数量
            String fileNumber = String.valueOf(bizObject.GetValue("fileNumber"));
            //归档形式
            String filingForm = (String)bizObject.GetValue("filingForm");
            //归档审查公司
            String reviewCompany = (String)bizObject.GetValue("reviewCompany");
            //送审日期
            String reviewDate = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("reviewDate"), dateFormatStr));
            //审查人
            String reviewPeople = (String)bizObject.GetValue("reviewPeople");
            //审查日期
            String reviewTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("reviewTime"), dateFormatStr));
            //审查结论
            String reviewConclusion = (String)bizObject.GetValue("reviewConclusion");
            //审查备注
            String reviewNote = (String)bizObject.GetValue("reviewNote");
            //归档编号
            String fileNo = (String)bizObject.GetValue("fileNo");
            //存放位置
            String location = (String)bizObject.GetValue("location");
            //收件人
            String recipient = (String)bizObject.GetValue("recipient");
            //收件日期
            String dateReceipt = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("dateReceipt"), dateFormatStr));
            //入库备注
            String treasuryNote = (String)bizObject.GetValue("treasuryNote");
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
//            int maxId = rebackService.getMaxId("OA_yw_实施流程_项目资料归档", "ID_3223");
            int maxId = rebackService.getMaxId("[ezoffice].[whir$t3223]", "whir$t3223_id");
            /*String sql = String.format(insetSql, maxId+1 , projectCode, businessCategory, biddingAgencyNo, NoName,
                    preparePeople, prepareDate, profileName, bindingMethod, fileNumber, filingForm, reviewCompany, reviewDate,
                    reviewPeople, reviewTime, reviewConclusion, reviewNote, fileNo, location, recipient, dateReceipt, treasuryNote);*/
            String sql = String.format(insetSql, maxId+1 , projectCode, biddingAgencyNo, businessCategory, businessCategoryId,
                    noName, preparePeople, prepareDate, profileName, bindingMethod, fileNumber, filingForm, reviewCompany, reviewDate,
                    reviewPeople, reviewTime,  reviewNote, fileNo, location, recipient, dateReceipt, treasuryNote, dataSources, businessId);
            logger.debug("项目资料电子归档申请回写SQL:"+sql);
            flag = rebackService.performSql(sql);
            modifyReviewResult(maxId+1, reviewConclusion);
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 项目书面档案归档申请表回写
     *
     * @param instanceId
     * @return
     */
    public boolean projectWrittenFiling(String instanceId, String workflowCode){
        boolean flag = false;
        /*String insetSql = "insert into OA_yw_实施流程_项目资料归档 (ID_3223, 项目代码, 归档服务类别, 招标代理号, " +
                "标段名称, 资料编制人, 资料编制日期, 资料名称, 装订方法, 归档数量, 归档形式, 归档审查公司, " +
                "送审日期, 审查人, 审查日期, 审查结论, 审查备注, 归档编号, 存放位置, 收件人, 收件日期, 入库备注)" +
                " values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";*/
        String insetSql = "insert into OA_yw_实施流程_项目资料归档 (ID_3223, 项目代码, 招标代理号, 业务类别, 业务类别全局ID, " +
                "标段名称, 资料编制人, 资料编制日期, 资料名称, 装订方法, 归档数量, 归档形式, 归档审查公司, " +
                "送审日期, 审查人, 审查日期,  审查备注, 归档编号, 存放位置, 收件人, 收件日期, 入库备注, 数据来源, 业务ID)" +
                " values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', " +
                "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')";
        try {
            InstanceData instanceData = new InstanceData(AppUtility.getEngine(), instanceId, User.AdministratorID);
            BizObject bizObject = instanceData.getBizObject();
            //项目代码
            String projectCode = (String)bizObject.GetValue("projectCode");
            //业务类别
            String businessCategory = (String)bizObject.GetValue("businessCategory");
            //业务类别编码
            String businessCategoryId = (String)bizObject.GetValue("businessCategoryId");
            //招标代理号
            String biddingAgencyNo = (String)bizObject.GetValue("biddingAgencyNo");
            //标段名称
            String sectionName = (String)bizObject.GetValue("sectionName");
            //资料编制人
            String preparePeople = (String)bizObject.GetValue("preparePeople");
            //资料编制日期
            String prepareTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("prepareTime"), dateFormatStr));
            //资料名称
            String profileName = (String)bizObject.GetValue("profileName");
            //装订方法
            String bindingMethod = (String)bizObject.GetValue("bindingMethod");
            //归档数量
            String fileNumber = String.valueOf(bizObject.GetValue("fileNumber"));
            //归档形式
            String filingForm = (String)bizObject.GetValue("filingForm");
            //归档审查公司
            String fileCompany = (String)bizObject.GetValue("fileCompany");
            //送审日期
            String fillDate = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("fillDate"), dateFormatStr));
            //审查人
            String reviewPeople = (String)bizObject.GetValue("reviewPeople");
            //审查日期
            String reviewTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("reviewTime"), dateFormatStr));
            //审查结论
            String reviewResult = (String)bizObject.GetValue("reviewResult");
            //审查备注
            String reviewNote = (String)bizObject.GetValue("reviewNote");
            //归档编号
            String fileNo = (String)bizObject.GetValue("fileNo");
            //存放位置
            String address = (String)bizObject.GetValue("address");
            //收件人
            String recipient = (String)bizObject.GetValue("recipient");
            //收件日期
            String recipientTime = clearDefaultDate(DateUtil.format((Date)bizObject.GetValue("recipientTime"), dateFormatStr));
            //入库备注
            String treasuryNote = (String)bizObject.GetValue("treasuryNote");
            //业务名称
//            String businessName = (String)bizObject.GetValue("businessName");
            //业务ID
            String businessId = (String)bizObject.GetValue("businessId");
//            int maxId = rebackService.getMaxId("OA_yw_实施流程_项目资料归档", "ID_3223");
            int maxId = rebackService.getMaxId("[ezoffice].[whir$t3223]", "whir$t3223_id");
            /*String sql = String.format(insetSql, maxId+1 , projectCode, businessCategory, biddingAgencyNo, sectionName,
                    preparePeople, prepareTime, profileName, bindingMethod, fileNumber, filingForm, fileCompany, fillDate,
                    reviewPeople, reviewTime, reviewResult, reviewNote, fileNo, address, recipient, recipientTime, treasuryNote);*/
            String sql = String.format(insetSql, maxId+1 , projectCode, biddingAgencyNo, businessCategory, businessCategoryId,
                    sectionName, preparePeople, prepareTime, profileName, bindingMethod, fileNumber, filingForm, fileCompany, fillDate,
                    reviewPeople, reviewTime, reviewNote, fileNo, address, recipient, recipientTime, treasuryNote, dataSources, businessId);
            logger.debug("项目书面档案归档申请回写SQL:"+sql);
            flag = rebackService.performSql(sql);
            modifyReviewResult(maxId+1, reviewResult);
        } catch (Exception e) {
            rebackService.saveWriteBackLog(workflowCode, e.toString(), instanceId, "fail");
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 修改审查结论
     * @param id
     * @param reviewResult
     * @return
     */
    private void modifyReviewResult(int id,String reviewResult){
        if(id>0 && StringUtils.isBlank(reviewResult)){
            return;
        }
        String sql = "update ezoffice.[whir$t3223] set whir$t3223_f6762 = '%s' where whir$t3223_id = '%s'";
        String sqls = String.format(sql, reviewResult, id);
        logger.debug("修改审查结论回写SQL:"+sqls);
        rebackService.performSql(sqls);
    }

    /**
     * 获取附件信息：多个附件通过分号拼接，名称和地址用逗号拼接
     *
     * @param objectId
     * @return
     * @throws Exception
     */
    private String getAttachmentByObjectId(String objectId) throws Exception {
        String attachment = "";
        String sql = "SELECT dataField,fileName,contentType,contentLength,storagePath,DownloadUrl FROM ot_attachment WHERE " +
                "BizObjectId='"+objectId+"'";
        DataTable data = SqlUtil.doQuery(sql);
        List<DataRow> attList = data.getEntityRows();
        for (int i = 0; i < attList.size(); i++) {
            //附件名称、附件上传路径
            if(i == attList.size()-1){
                attachment += attList.get(i).getString("fileName")+","+attList.get(i).getString("DownloadUrl");
            }else{
                attachment += attList.get(i).getString("fileName")+","+attList.get(i).getString("DownloadUrl")+";";
            }
        }
        return attachment;
    }

    //日期默认为1970-01-01时清除为空
    public static String clearDefaultDate(String strDate){
        if(StringUtils.isNotBlank(strDate)&&strDate.equals("1970-01-01")){
            return "";
        }else{
            return strDate;
        }
    }

}
